﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class educoursesedit : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
            SetData();
    }

    //button：提交
    protected void Button1_Click(object sender, EventArgs e)
    {
        //不能一次提交，有重复和不重复的课程名 ，要行处理

    }

  
    //行更新
    protected void UltraWebGrid1_UpdateRow(object sender, Infragistics.WebUI.UltraWebGrid.RowEventArgs e)
    {
        if (e.Row.Cells[2].Value == null)
        {
            //课程名不能为空 
            RegisterStartupScript("message", "<script language='javascript' defer>alert('课程名称不能为空！');</script>");
        }
        else
        {
            SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["smsConnectionString"].ConnectionString);


            SqlDataAdapter da = new SqlDataAdapter("select * from CourseInfo where IsDeleted<>1", objConnection);
            DataSet ds = new DataSet();
            da.Fill(ds, "CourseInfo");
            DataTable dt = ds.Tables["CourseInfo"];

            if (e.Row.Cells[1].Value != null)
            {
                //更新

                objConnection.Open();
                //添加表CourseInfo，并返回值
                //创建并初始化一个objCommand对象
                SqlCommand objCommand = new SqlCommand();
                objCommand.Connection = objConnection;
                objCommand.CommandText = "UpdateCourse";
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Clear();

                //写参数
                SqlParameter objParameter = new SqlParameter("@CourseName", SqlDbType.NVarChar);
                objCommand.Parameters.Add(objParameter);
                objParameter.Direction = ParameterDirection.Input;
                objParameter.Value = e.Row.Cells[2].Value;

                objParameter = new SqlParameter("@CourseDes", SqlDbType.NVarChar);
                objCommand.Parameters.Add(objParameter);
                objParameter.Direction = ParameterDirection.Input;
                objParameter.Value = e.Row.Cells[3].Value;

                objParameter = new SqlParameter("@CourseID", SqlDbType.NVarChar);
                objCommand.Parameters.Add(objParameter);
                objParameter.Direction = ParameterDirection.Input;
                objParameter.Value = e.Row.Cells[0].Value;

                objCommand.ExecuteNonQuery();
                objConnection.Close();

            }
            else
            {
                //页面验证
                DataRow[] rows = dt.Select("CourseName = '" + e.Row.Cells[2].Value.ToString() + " '");
                if (rows.Length > 0)
                {
                    //数据库中已经有有效的此课程名，重名问题，提示用户输入错误
                    RegisterStartupScript("message", "<script language='javascript' defer>alert('表中已有相同的课程！');</script>");
                }
                else
                {  //开始添加
                    objConnection.Open();
                    ////开始一个本地事务
                    SqlTransaction trans = objConnection.BeginTransaction(IsolationLevel.Serializable);

                    //创建并初始化一个objCommand对象
                    SqlCommand objCommand = new SqlCommand();

                    objCommand.Connection = objConnection;
                    objCommand.Transaction = trans;
                    try
                    {

                        //添加表CourseInfo，并返回值
                        objCommand.CommandText = "CreateCourse";
                        objCommand.CommandType = CommandType.StoredProcedure;
                        objCommand.Parameters.Clear();

                        //写参数
                        SqlParameter objParameter = new SqlParameter("@CourseName", SqlDbType.NVarChar);
                        objCommand.Parameters.Add(objParameter);
                        objParameter.Direction = ParameterDirection.Input;
                        objParameter.Value = e.Row.Cells[2].Value;

                        objParameter = new SqlParameter("@CourseDes", SqlDbType.NVarChar);
                        objCommand.Parameters.Add(objParameter);
                        objParameter.Direction = ParameterDirection.Input;
                        objParameter.Value = e.Row.Cells[3].Value;


                        SqlParameter objOutputParameter = new SqlParameter("@CourseID", SqlDbType.Int);
                        objCommand.Parameters.Add(objOutputParameter);
                        objOutputParameter.Direction = ParameterDirection.Output;

                        objCommand.ExecuteNonQuery();
                        int nCourseID = (int)objOutputParameter.Value;                                        

                        trans.Commit();
                    }
                    catch (SqlException)
                    {
                        //退出事务处理（撤销更改）
                        if (trans != null)
                            trans.Rollback();
                    }
                    finally
                    { objConnection.Close(); }
                }
            }
            SetData();
        }
    }

    public void SetData()
    {
        ConnectionStringSettings connObj = ConfigurationManager.ConnectionStrings["smsConnectionString"];
        string connectionString = connObj.ConnectionString;
        SqlConnection objConnection = new SqlConnection(connectionString);

        SqlDataAdapter da = new SqlDataAdapter("SELECT CourseID, CourseName AS 课程名称, CourseDes AS 课程描述 FROM CourseInfo where IsDeleted<>1", objConnection);

        DataSet ds = new DataSet();
        da.Fill(ds, "课程表");
        DataTable dt = ds.Tables["课程表"];
        DataView view = new DataView(dt);

        this.UltraWebGrid1.DataSource = view;
        this.UltraWebGrid1.DataKeyField = "CourseID";
        this.UltraWebGrid1.DataBind();
        this.UltraWebGrid1.Columns[1].Hidden = true;
        this.UltraWebGrid1.Columns[2].Width = 320;
        this.UltraWebGrid1.Columns[3].Width =320; 
    }
   
    //删除
    protected void Button2_Click1(object sender, EventArgs e)
    {
        SqlConnection objConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["smsConnectionString"].ConnectionString);

        SqlDataAdapter da = new SqlDataAdapter("select * from CourseInfo", objConnection);
        SqlCommandBuilder cb = new SqlCommandBuilder(da);
        DataSet ds = new DataSet();
        da.Fill(ds, "CourseInfo");
        DataTable dt = ds.Tables["CourseInfo"];
        DataRow[] rows = dt.Select("CourseID = "+Convert.ToInt32(this.UltraWebGrid1.DisplayLayout.ActiveRow.DataKey)+"");
        foreach (DataRow row in rows)
            row["IsDeleted"] = true;
        da.Update(dt);

        SetData();
    }
}